/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import pojo.SanPham_pojo;
import util.MySQLDataAccessHelper;

/**
 *
 * @author Rocker000
 */
public class SanPham_dao 
{
    public static ArrayList<SanPham_pojo> TimKiemTheoKeyVaLoai(String key, String loai)
    {
        ArrayList<SanPham_pojo> ds = new ArrayList<SanPham_pojo>();
        try
        {
            String sql = "select*from sieuthidienmay.sanpham where sanpham.tensanpham like N'%"+key+"%' and maloaisanpham = '"+loai+"' ";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.ExeCuteQuery(sql);
            while(rs.next())
            {
                SanPham_pojo temp = new SanPham_pojo();
                temp.setTenSanPham(rs.getString("tensanpham"));
                temp.setGia(rs.getDouble("gia"));
                temp.setHieu(rs.getString("hieu"));
                temp.setImage(rs.getString("Image"));
                temp.setKichThuoc(rs.getFloat("kichthuoc"));
                temp.setMaSanPham(rs.getString("masanpham"));
                temp.setSoLuongTon(rs.getInt("soluongton"));
                temp.setSoLuotMua(rs.getInt("luotmua"));
                temp.setChiTietSanPham(rs.getString("chitietsanpham"));
                temp.setMaLoai(rs.getString("maloaisanpham"));
                ds.add(temp);
            }
            helper.Close();
        }
        catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
    
    
    public static ArrayList<SanPham_pojo> TimKiemTheoKey(String key)
    {
        ArrayList<SanPham_pojo> ds = new ArrayList<SanPham_pojo>();
        try
        {
            String sql = "select*from sieuthidienmay.sanpham where sanpham.tensanpham like N'%"+key+"%' ";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.ExeCuteQuery(sql);
            while(rs.next())
            {
                SanPham_pojo temp = new SanPham_pojo();
                temp.setTenSanPham(rs.getString("tensanpham"));
                temp.setGia(rs.getDouble("gia"));
                temp.setHieu(rs.getString("hieu"));
                temp.setImage(rs.getString("Image"));
                temp.setKichThuoc(rs.getFloat("kichthuoc"));
                temp.setMaSanPham(rs.getString("masanpham"));
                temp.setSoLuongTon(rs.getInt("soluongton"));
                temp.setSoLuotMua(rs.getInt("luotmua"));
                temp.setChiTietSanPham(rs.getString("chitietsanpham"));
                temp.setMaLoai(rs.getString("maloaisanpham"));
                ds.add(temp);
            }
            helper.Close();
        }
        catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
    
    public static ArrayList<SanPham_pojo> TimKiemTheoHieuVaDanhMuc(String hieu,String madanhmuc)
    {
        ArrayList<SanPham_pojo> ds = new ArrayList<SanPham_pojo>();
        try
        {
            String sql = "select*from sanpham where sanpham.hieu=N'"+hieu+"'";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.ExeCuteQuery(sql);
            while(rs.next())
            {
                SanPham_pojo temp = new SanPham_pojo();
                temp.setTenSanPham(rs.getString("tensanpham"));
                temp.setGia(rs.getDouble("gia"));
                temp.setHieu(rs.getString("hieu"));
                temp.setImage(rs.getString("Image"));
                temp.setKichThuoc(rs.getFloat("kichthuoc"));
                temp.setMaSanPham(rs.getString("masanpham"));
                temp.setSoLuongTon(rs.getInt("soluongton"));
                temp.setSoLuotMua(rs.getInt("luotmua"));
                temp.setChiTietSanPham(rs.getString("chitietsanpham"));
                temp.setMaLoai(rs.getString("maloaisanpham"));
                ds.add(temp);
            }
            helper.Close();
        }
        catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
    
    public static ArrayList<SanPham_pojo> TimKiemTheoGia(double begin,double end)
    {
        ArrayList<SanPham_pojo> ds = new ArrayList<SanPham_pojo>();
        try
        {
            String sql = "select*from sanpham where sanpham.gia>="+begin+" and sanpham.gia<"+end+"";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.ExeCuteQuery(sql);
            while(rs.next())
            {
                SanPham_pojo temp = new SanPham_pojo();
                temp.setTenSanPham(rs.getString("tensanpham"));
                temp.setGia(rs.getDouble("gia"));
                temp.setHieu(rs.getString("hieu"));
                temp.setImage(rs.getString("Image"));
                temp.setKichThuoc(rs.getFloat("kichthuoc"));
                temp.setMaSanPham(rs.getString("masanpham"));
                temp.setSoLuongTon(rs.getInt("soluongton"));
                temp.setSoLuotMua(rs.getInt("luotmua"));
                temp.setChiTietSanPham(rs.getString("chitietsanpham"));
                temp.setMaLoai(rs.getString("maloaisanpham"));
                ds.add(temp);
            }
            helper.Close();
        }
        catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
    
    public static ArrayList<SanPham_pojo> LaySanPhamHot()
    {
        ArrayList<SanPham_pojo> ds = new ArrayList<SanPham_pojo>();
        try
        {
            String sql = "select*from sanpham order by luotmua desc limit 16";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.ExeCuteQuery(sql);
            while(rs.next())
            {
                SanPham_pojo temp = new SanPham_pojo();
                temp.setTenSanPham(rs.getString("tensanpham"));
                temp.setGia(rs.getDouble("gia"));
                temp.setHieu(rs.getString("hieu"));
                temp.setImage(rs.getString("Image"));
                temp.setKichThuoc(rs.getFloat("kichthuoc"));
                temp.setMaSanPham(rs.getString("masanpham"));
                temp.setSoLuongTon(rs.getInt("soluongton"));
                temp.setSoLuotMua(rs.getInt("luotmua"));
                temp.setChiTietSanPham(rs.getString("chitietsanpham"));
                temp.setMaLoai(rs.getString("maloaisanpham"));
                ds.add(temp);
            }
            helper.Close();
        }
        catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
    
    public static SanPham_pojo LaySanPhamTheoMa(String masanpham)
    {
        SanPham_pojo sp = new SanPham_pojo();
        try
        {
            String sql = "select*from sanpham where masanpham='"+masanpham+"'";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.Open();
            ResultSet rs = helper.ExeCuteQuery(sql);
            while(rs.next())
            {
                sp.setTenSanPham(rs.getString("tensanpham"));
                sp.setGia(rs.getDouble("gia"));
                sp.setHieu(rs.getString("hieu"));
                sp.setImage(rs.getString("Image"));
                sp.setKichThuoc(rs.getFloat("kichthuoc"));
                sp.setMaSanPham(rs.getString("masanpham"));
                sp.setSoLuongTon(rs.getInt("soluongton"));
                sp.setSoLuotMua(rs.getInt("luotmua"));
                sp.setChiTietSanPham(rs.getString("Chitietsanpham"));
                sp.setMaLoai(rs.getString("maloaisanpham"));
            }
            helper.Close();
        }
        catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return sp;
    }
    public static ArrayList<SanPham_pojo> LaySanPhamTheoMaLoai(String maloai)
    {
        ArrayList<SanPham_pojo> ds = new ArrayList<SanPham_pojo>();
        String sql = "select*from sanpham where maloaisanpham='"+maloai+"'";
        try
        {
            MySQLDataAccessHelper helper =new MySQLDataAccessHelper();
            helper.Open();
            
            ResultSet rs = helper.ExeCuteQuery(sql);
            while(rs.next())
            {
                SanPham_pojo sp = new SanPham_pojo();
                sp.setTenSanPham(rs.getString("tensanpham"));
                sp.setGia(rs.getDouble("gia"));
                sp.setHieu(rs.getString("hieu"));
                sp.setImage(rs.getString("Image"));
                sp.setKichThuoc(rs.getFloat("kichthuoc"));
                sp.setMaSanPham(rs.getString("masanpham"));
                sp.setSoLuongTon(rs.getInt("soluongton"));
                sp.setSoLuotMua(rs.getInt("luotmua"));
                ds.add(sp);
            }
            
            helper.Close();
        }
        catch(Exception ex)
        {
             System.out.println(ex.getMessage());
        }
        return ds;
    }
    
    public static int TinhSoLuongTrang(ArrayList<SanPham_pojo> ds)
    {
        int sotrang = 0;
        int sosanpham = ds.size();
        int sosanphamtrentrang = ThamSo_dao.SoSanPhanTrenTrang();
        sotrang = sosanpham/sosanphamtrentrang;
        if(sosanpham%sosanphamtrentrang !=0)
            sotrang ++;
        return sotrang;
    }
    public static ArrayList<SanPham_pojo> LayDanhSachCoPhanTrang(ArrayList<SanPham_pojo> ds,int trang)
    {
        ArrayList<SanPham_pojo> temp = new ArrayList<SanPham_pojo>();
        int sosanphamtrentrang = ThamSo_dao.SoSanPhanTrenTrang();
        int batdau =(trang-1)*sosanphamtrentrang ;
        if(batdau+sosanphamtrentrang>ds.size())
        {
            for(int i = batdau;i< ds.size();i++)
            {
                SanPham_pojo sp = new SanPham_pojo();
                
               
                sp.setTenSanPham(ds.get(i).getTenSanPham());
                sp.setGia(ds.get(i).getGia());
                sp.setHieu(ds.get(i).getHieu());
                sp.setImage(ds.get(i).getImage());
                sp.setKichThuoc(ds.get(i).getKichThuoc());
                sp.setMaSanPham(ds.get(i).getMaSanPham());
                sp.setSoLuongTon(ds.get(i).getSoLuongTon());
                sp.setSoLuotMua(ds.get(i).getSoLuotMua());
                sp.setChiTietSanPham(ds.get(i).getChiTietSanPham());
                temp.add(sp);
                                
            }
        }
        else
        {
            for(int i = batdau;i< batdau+sosanphamtrentrang;i++)
            {
                SanPham_pojo sp = new SanPham_pojo();
                
               
                sp.setTenSanPham(ds.get(i).getTenSanPham());
                sp.setGia(ds.get(i).getGia());
                sp.setHieu(ds.get(i).getHieu());
                sp.setImage(ds.get(i).getImage());
                sp.setKichThuoc(ds.get(i).getKichThuoc());
                sp.setMaSanPham(ds.get(i).getMaSanPham());
                sp.setSoLuongTon(ds.get(i).getSoLuongTon());
                sp.setSoLuotMua(ds.get(i).getSoLuotMua());
                sp.setChiTietSanPham(ds.get(i).getChiTietSanPham());
                temp.add(sp);
            } 
        }
        return temp;
    }
    
    public static int ThemSanPham(SanPham_pojo sp)
    {
        int kq = -1;
        
        String sql = "INSERT INTO `sieuthidienmay`.`sanpham` (`MaSanPham`, `TenSanPham`, `Gia`, `Hieu`, `SoLuongTon`,`LuotMua`, `KichThuoc`, `MaLoaiSanPham`, `Image`, `ChiTietSanPham`) VALUES ('"+sp.getMaSanPham()+"', '"+sp.getTenSanPham()+"', '"+sp.getGia()+"', '"+sp.getHieu()+"', "+sp.getSoLuongTon()+", "+sp.getSoLuotMua()+", "+sp.getKichThuoc()+", '"+sp.getMaLoai()+"', '"+sp.getImage()+"', '"+sp.getChiTietSanPham()+"')";
        try
        {
            String str=String.valueOf("INSERT INTO `sieuthidienmay`.`sanpham` (`MaSanPham`, `TenSanPham`, `Gia`, `Hieu`, `SoLuongTon`, `KichThuoc`, `MaLoaiSanPham`, `LuotMua`, `Image`, `ChiTietSanPham`) VALUES ('"+sp.getMaSanPham()+"', '"+sp.getTenSanPham()+"', '"+sp.getGia()+"', '"+sp.getHieu()+"',"+sp.getSoLuongTon()+", "+sp.getKichThuoc()+", '"+sp.getMaLoai()+"', "+sp.getSoLuotMua()+", '"+sp.getImage()+"', '"+sp.getChiTietSanPham()+"')");
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.Open();
            kq = helper.ExeCuteUpdate(str);
            helper.Close();
        }
        catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        return kq;
    }
    
    
}
